﻿#region Dont Look This

// /*************************************************************************************
// 
// * 文件名称：  ExcelHelp.cs
// 
// * 作       者：  jieaido@outlook.com
// 
// * 创建日期：  2020/04/06/20:56
// 
// * 修改日期:     2020/04/06/20:56
// 
// * 功能描述：
// 
// 
// 
//                               Copyright (c) Jieaido. AllRights Reserved
// 
// *************************************************************************************/

#endregion

using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using HelpLibrary.SQlite;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using LicenseContext = OfficeOpenXml.LicenseContext;

namespace HelpLibrary.Excel
{
    public class ExcelHelp
    {
        /// <summary>
        ///     自己写的一个导出excel的泛型方法,将来会弃用使用专用方法
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="list">泛型列表</param>
        public static void ExportToExcel<T>(List<T> list, List<MyStatisticsModel> myStatistics = null)
        {
            var dialog = new SaveFileDialog();
            dialog.Filter = "Microsoft Excel 2013|*.xlsx";
            dialog.DefaultExt = "xlsx";
            dialog.AddExtension = true;
            dialog.Title = "Save Excel";
            var result = dialog.ShowDialog();
            if (result == DialogResult.OK)
            {
                FileStream stream = null;
                try
                {
                    stream = new FileStream(dialog.FileName, FileMode.Create);
                }
                catch (IOException ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }

                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                var package = new ExcelPackage(stream);
                using (package)
                {
                    package.Workbook.Worksheets.Add("Fuck");
                    var sheet = package.Workbook.Worksheets[0];

                    var itemType = typeof(T);
                    var itemPropertyies = itemType.GetProperties();

                    #region 设置分类栏
                    using (var range = sheet.Cells[1, 1, 1, itemPropertyies.Length])
                    {
                        range.Merge = true;
                        range.Value = "导出的数据表";
                        range.Style.Fill.PatternType = ExcelFillStyle.Gray0625;
                        range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        //  range.Style.Fill.BackgroundColor.SetColor(Color.Yellow);
                        range.Style.Font.Size = 24;
                        range.Style.Font.Bold = true;
                        // range.Style.Font.Color.SetColor(Color.White);
                    }

              

                    using (var range = sheet.Cells[2, 1, 2, 7])
                    {
                        range.Merge = true;
                        range.Value = "柜容记录";
                        range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                        range.Style.Font.Size = 16;
                        range.Style.Font.Bold = true;
                    }

                    using (var range = sheet.Cells[2, 8, 2, 13])
                    {
                        range.Merge = true;
                        range.Value = "柜容差";
                        range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                        range.Style.Font.Size = 16;
                        range.Style.Font.Bold = true;
                    }
                    //设置边框
                    using (var r = sheet.Cells[1, 1, list.Count + 4, itemPropertyies.Length])
                    {
                        r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                        r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                        r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                        r.Style.Border.Right.Style = ExcelBorderStyle.Thin;

                        r.Style.Border.Top.Color.SetColor(Color.Black);
                        r.Style.Border.Bottom.Color.SetColor(Color.Black);
                        r.Style.Border.Left.Color.SetColor(Color.Black);
                        r.Style.Border.Right.Color.SetColor(Color.Black);
                    }

                    #endregion


                    for (var i = 0; i < itemPropertyies.Length; i++)
                    {
                        // var attribute = itemPropertyies[i].GetCustomAttributes();
                        var attribute = itemPropertyies[i].GetCustomAttributes(typeof(DisplayNameAttribute), true)
                            .Cast<DisplayNameAttribute>().SingleOrDefault();
                        sheet.Cells[3, i + 1].Value = attribute.DisplayName;
                    }

                    #region 填入主数据

                    for (var rowindex = 0; rowindex < list.Count; rowindex++)
                        for (var cloumsindex = 0; cloumsindex < itemPropertyies.Length; cloumsindex++)
                            if (cloumsindex == 8 || cloumsindex == 10 || cloumsindex == 12)
                            {
                                var temp = itemPropertyies[cloumsindex].GetValue(list[rowindex], null);
                                var cell = sheet.Cells[rowindex + 4, cloumsindex + 1];
                                var cells = sheet.Cells[rowindex + 4, cloumsindex, rowindex + 4, cloumsindex + 1];
                                switch (temp.ToString())
                                {
                                    case "A":
                                        cell.Value = "甲班";
                                        cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
                                        cells.Style.Fill.BackgroundColor.SetColor(Color.Coral);

                                        break;
                                    case "B":

                                        cell.Value = "乙班";
                                        cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
                                        cells.Style.Fill.BackgroundColor.SetColor(Color.Yellow);

                                        break;
                                    case "C":

                                        cell.Value = "丙班";
                                        cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
                                        cells.Style.Fill.BackgroundColor.SetColor(Color.GreenYellow);

                                        break;

                                    case "D":
                                        cell.Value = "丁班";
                                        cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
                                        cells.Style.Fill.BackgroundColor.SetColor(Color.CornflowerBlue);

                                        break;
                                }
                            }
                            else
                            {
                                sheet.Cells[rowindex + 4, cloumsindex + 1].Value =
                                    itemPropertyies[cloumsindex].GetValue(list[rowindex], null);
                            }

                    #endregion

                    #region 填入统计数据

                    if (myStatistics != null)
                    {
                        package.Workbook.Worksheets.Add("统计表");
                        var tongjisheet = package.Workbook.Worksheets[1];
                        var itemPropertyies2 = typeof(MyStatisticsModel).GetProperties();
                        for (var i = 0; i < itemPropertyies2.Length; i++)
                        {
                            var attribute = itemPropertyies2[i].GetCustomAttributes(typeof(DisplayNameAttribute), true)
                                .Cast<DisplayNameAttribute>().SingleOrDefault();
                            tongjisheet.Cells[1, i + 1].Value = attribute.DisplayName;
                        }

                        for (var rowindex = 0; rowindex < myStatistics.Count; rowindex++)
                        for (var cloumsindex = 0; cloumsindex < itemPropertyies2.Length; cloumsindex++)
                            tongjisheet.Cells[rowindex + 2, cloumsindex + 1].Value =
                                itemPropertyies2[cloumsindex].GetValue(myStatistics[rowindex], null);
                        tongjisheet.Column(1).Style.Numberformat.Format = "yyyy-m-d";
                    }

                    #endregion


                    sheet.Column(1).Style.Numberformat.Format = "yyyy-m-d";
                    package.Save();
                }
            }
        }
    }
}